package com.example.homework8.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.homework8.bean.PlanInfo;
import com.example.homework8.bean.PlanInfo;

import java.util.ArrayList;

public class PlanDBHelper extends SQLiteOpenHelper {
    private static final String TAG = "PlanDBHelper"; //Log提示信息
    private static final String DB_NAME = "plan.db"; // 数据库的名称
    private static final int DB_VERSION = 1; // 数据库的版本号
    private static PlanDBHelper mHelper = null; // 数据库帮助器的实例
    private SQLiteDatabase mDB = null; // 数据库的实例
    public static final String TABLE_NAME = "record_info"; // 表的名称

    private PlanDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    private PlanDBHelper(Context context, int version) {
        super(context, DB_NAME, null, version);
    }

    public static PlanDBHelper getInstance(Context context, int version) {
        if (version > 0 && mHelper == null) {
            mHelper = new PlanDBHelper(context, version);
        } else if (mHelper == null) {
            mHelper = new PlanDBHelper(context);
        }
        return mHelper;
    }

    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (mDB == null || !mDB.isOpen()) {
            mDB = mHelper.getReadableDatabase();
        }
        return mDB;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (mDB == null || !mDB.isOpen()) {
            mDB = mHelper.getWritableDatabase();
        }
        return mDB;
    }

    // 关闭数据库连接
    public void closeLink() {
        if (mDB != null && mDB.isOpen()) {
            mDB.close();
            mDB = null;
        }
    }

    // 创建数据库，执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d(TAG, "onCreate");
        String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
        Log.d(TAG, "drop_sql:" + drop_sql);
        db.execSQL(drop_sql);
        String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                + "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
                + "name VARCHAR NOT NULL," + "info VARCHAR NOT NULL,"
                + "cate VARCHAR NOT NULL," + "createtime VARCHAR NOT NULL,"
                + "username VARCHAR NOT NULL"
                + ");";
        Log.d(TAG, "create_sql:" + create_sql);
        db.execSQL(create_sql);
    }

    // 修改数据库，执行表结构变更语句
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade oldVersion=" + oldVersion + ", newVersion=" + newVersion);
        if (newVersion > 1) {
            //Android的ALTER命令不支持一次添加多列，只能分多次添加
            String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "add_time VARCHAR;";
            Log.d(TAG, "alter_sql:" + alter_sql);
            db.execSQL(alter_sql);
            alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "is_deleted INTEGER;";
            Log.d(TAG, "alter_sql:" + alter_sql);
            db.execSQL(alter_sql);
        }
    }

    // 根据指定条件删除表记录
    public int delete(String condition) {
        // 执行删除记录动作，该语句返回删除记录的数目
        return getWritableDatabase().delete(TABLE_NAME, condition, null);
    }

    // 删除该表的所有记录
    public int deleteAll() {
        // 执行删除记录动作，该语句返回删除记录的数目
        return getWritableDatabase().delete(TABLE_NAME, "1=1", null);
    }

    // 往该表添加一条记录
    public long insert(PlanInfo info) {
        ArrayList<PlanInfo> infoArray = new ArrayList<PlanInfo>();
        infoArray.add(info);
        return insert(infoArray);
    }

    // 往该表添加多条记录
    public long insert(ArrayList<PlanInfo> infoArray) {
        long result = -1;
        for (PlanInfo info : infoArray) {
            // 如果存在相同rowid的记录，则更新记录
            if (info.rowid > 0) {
                String condition = String.format("rowid='%d'", info.rowid);
                update(info, condition);
                result = info.rowid;
                continue;
            }
            ContentValues cv = new ContentValues();
            cv.put("name", info.name);
            cv.put("createtime", info.createtime);
            cv.put("info", info.info);
            cv.put("username", info.username);
            cv.put("cate", info.cate);

            // 执行插入记录动作，该语句返回插入记录的行号
            result = getWritableDatabase().insert(TABLE_NAME, "", cv);
            // 添加成功后返回行号，失败后返回-1
            if (result == -1) {
                return result;
            }
        }
        return result;
    }

    // 根据条件更新指定的表记录
    public int update(PlanInfo info, String condition) {
        ContentValues cv = new ContentValues();
        cv.put("name", info.name);
        cv.put("createtime", info.createtime);
        cv.put("info", info.info);
        cv.put("username", info.username);
        cv.put("cate", info.cate);
        return getWritableDatabase().update(TABLE_NAME, cv, condition, null);
    }

    public int update(PlanInfo info) {
        return update(info, "rowid=" + info.rowid);
    }

    // 根据指定条件查询记录，并返回结果数据队列
    public ArrayList<PlanInfo> query(String condition) {
        String sql = String.format("select * " +
                " from %s where %s;", TABLE_NAME, condition);
        Log.d(TAG, "query sql: " + sql);
        ArrayList<PlanInfo> infoArray = new ArrayList<PlanInfo>();
        Cursor cursor = getReadableDatabase().rawQuery(sql, null);
        while (cursor.moveToNext()) {
            PlanInfo info = new PlanInfo();
            info.rowid = cursor.getLong(0);
            info.name = cursor.getString(1);
            info.info = cursor.getString(2);
            info.cate = cursor.getString(3);
            info.createtime = cursor.getString(4);
            info.username   = cursor.getString(5);
            infoArray.add(info);
        }
        cursor.close(); // 查询完毕，关闭游标
        return infoArray;
    }

    public ArrayList<PlanInfo> getAll() {
        String sql = String.format("select *" +
                " from %s ;", TABLE_NAME);
        Log.d(TAG, "query sql: " + sql);
        ArrayList<PlanInfo> infoArray = new ArrayList<PlanInfo>();
        Cursor cursor = getReadableDatabase().rawQuery(sql, null);
        while (cursor.moveToNext()) {
            PlanInfo info = new PlanInfo();
            info.rowid = cursor.getLong(0);
            info.name = cursor.getString(1);
            info.info = cursor.getString(2);
            info.cate = cursor.getString(3);
            info.createtime = cursor.getString(4);
            info.username = cursor.getString(5);

            infoArray.add(info);
        }
        cursor.close(); // 查询完毕，关闭游标
        return infoArray;
    }


    // 根据手机号码查询指定记录
    public PlanInfo queryById(long rowid) {
        PlanInfo info = null;
        ArrayList<PlanInfo> infoArray = query(String.format("rowid='%d'", rowid));
        if (infoArray.size() > 0) {
            info = infoArray.get(0);
        }
        return info;
    }

}